<?php

namespace app\store\model;

use think\Model;

class UserPropLogModel extends Model
{
    protected $connection = 'db_platform_log';

    protected $table = 'user_prop_log';

    // 弹头的道具id
    const DanTou = [10016, 10018];

    // 时间类型
    protected $date_type = null;

    // 搜索时间
    protected $date_range = null;

    // 充值的用户ids子查询
    protected $trade_sub_sql = null;

    // 筛选的用户字段和值
    protected $user_field = null;
    protected $user_value_start = null;
    protected $user_value_end = null;

    /**
     * UserPropLogModel constructor.
     * @param array $data
     * @throws \think\exception\DbException
     */
    public function __construct($data = [])
    {
        parent::__construct($data);
        $time_field = request()->get('time_field');
        $time_value = request()->get('time_value');
        $this->user_field = request()->get('user_field');
        $this->user_value_start = request()->get('user_value_start');
        $this->user_value_end = request()->get('user_value_end');
        // 获取具体时间 单天
        if (!empty($time_field) and !empty($time_value)) {
            $date_range = [$time_value, $time_value . ' 23:59:59'];
        } else {
            $date_range = [date('Y-m-d'), date('Y-m-d 23:59:59')];
        }

        $this->date_range = $date_range;

        $this->date_type = !empty($time_field)?$time_field:'RegisterDate';

        $this->trade_sub_sql = TradeModel::getUserIdsSubSql($this->date_range);

    }

    /**
     * 获取道具使用
     * @param $where
     * @return array|\PDOStatement|string|\think\Collection
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getProp($where)
    {
        // 筛选炮倍
        if (!is_null($this->user_value_start) and !is_null($this->user_value_end)) {
            if ($this->user_field == 'cannon') {

                // 新增还是活跃
                if ($this->date_type == 'RegisterDate') {
                    // 所有的
                    $query = self::where($where)
                        ->fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                        ->alias('l')
                        ->group('l.PropID')
                        ->leftJoin('qpaccountsdb.user_game_info g', 'l.UserID = g.UserID')
                        ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID')

                        ->where('l.VarCounts', '<', 0)// 小于0表示使用
                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->where('a.RegisterDate', 'between', $this->date_range);

                    // 充值的
                    $query2 = self::where($where)
                        ->fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                        ->alias('l')
                        ->group('l.PropID')
                        ->leftJoin('qpaccountsdb.user_game_info g', 'l.UserID = g.UserID')
                        ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID')

                        ->where('l.VarCounts', '<', 0)// 小于0表示使用
                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->where('a.RegisterDate', 'between', $this->date_range)
                        ->where('l.UserID in'.$this->trade_sub_sql);
                } else {
                    // 登录子查询
                    $login_sub_sql = UserLoginModel::getUserIdsSubSql($this->date_range);

                    $query = self::where($where)
                        ->fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                        ->alias('l')
                        ->group('l.PropID')
                        ->leftJoin('qpaccountsdb.user_game_info g', 'l.UserID = g.UserID')

                        ->where('l.VarCounts', '<', 0)// 小于0表示使用
                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->where('l.UserID in'.$login_sub_sql);

                    // 充值的
                    $query2 = self::where($where)
                        ->fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                        ->alias('l')
                        ->group('l.PropID')
                        ->leftJoin('qpaccountsdb.user_game_info g', 'l.UserID = g.UserID')

                        ->where('l.VarCounts', '<', 0)// 小于0表示使用
                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->where('l.UserID in'.$login_sub_sql)
                        ->where('l.UserID in'.$this->trade_sub_sql);
                }

            } else {// 不筛选炮倍
                // 新增还是活跃
                if ($this->date_type == 'RegisterDate') {
                    // 所有的
                    $query = self::where($where)
                        ->fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                        ->alias('l')
                        ->group('l.PropID')
                        ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID')

                        ->where('l.VarCounts', '<', 0)// 小于0表示使用
                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->where('a.RegisterDate', 'between', $this->date_range);

                    // 充值的
                    $query2 = self::where($where)
                        ->fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                        ->alias('l')
                        ->group('l.PropID')
                        ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID')

                        ->where('l.VarCounts', '<', 0)// 小于0表示使用
                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->where('a.RegisterDate', 'between', $this->date_range)
                        ->where('l.UserID in'.$this->trade_sub_sql);
                } else {
                    // 登录子查询
                    $login_sub_sql = UserLoginModel::getUserIdsSubSql($this->date_range);

                    $query = self::where($where)
                        ->fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                        ->alias('l')
                        ->group('l.PropID')
                        ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID')

                        ->where('l.VarCounts', '<', 0)// 小于0表示使用
                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->where('l.UserID in'.$login_sub_sql);

                    // 充值的
                    $query2 = self::where($where)
                        ->fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                        ->alias('l')
                        ->group('l.PropID')
                        ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID')

                        ->where('l.VarCounts', '<', 0)// 小于0表示使用
                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->where('l.UserID in'.$login_sub_sql)
                        ->where('l.UserID in'.$this->trade_sub_sql);
                }
            }
        } else {
            // 新增还是活跃
            if ($this->date_type == 'RegisterDate') {
                // 所有的
                $query = self::fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                    ->alias('l')
                    ->group('l.PropID')
                    ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID')

                    ->where('l.VarCounts', '<', 0)// 小于0表示使用
                    ->where('l.RecordTime', 'between', $this->date_range)
                    ->where('a.RegisterDate', 'between', $this->date_range);

                // 充值的
                $query2 = self::fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                    ->alias('l')
                    ->group('l.PropID')
                    ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID')

                    ->where('l.VarCounts', '<', 0)// 小于0表示使用
                    ->where('l.RecordTime', 'between', $this->date_range)
                    ->where('a.RegisterDate', 'between', $this->date_range)
                    ->where('l.UserID in'.$this->trade_sub_sql);
            } else {
                // 登录子查询
                $login_sub_sql = UserLoginModel::getUserIdsSubSql($this->date_range);

                $query = self::fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                    ->alias('l')
                    ->group('l.PropID')

                    ->where('l.VarCounts', '<', 0)// 小于0表示使用
                    ->where('l.RecordTime', 'between', $this->date_range)
                    ->where('l.UserID in'.$login_sub_sql);

                $query2 = self::fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                    ->alias('l')
                    ->group('l.PropID')

                    ->where('l.VarCounts', '<', 0)// 小于0表示使用
                    ->where('l.RecordTime', 'between', $this->date_range)
                    ->where('l.UserID in'.$login_sub_sql)
                    ->where('l.UserID in'.$this->trade_sub_sql);

            }
        }

        $data = $query->select();
        $data2 = $query2->select();
        // echo $query->getLastSql().'<br/>';die;
        // echo $query2->getLastSql();
        return [$data, $data2];
    }

    /**获取弹头赠送统计
     * @param $where
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getDanTou($where)
    {

        // 弹头赠送的条件
        $where_dan_tou = [
            ['l.VarCounts', '<', 0],// 小于0表示使用
            ['l.PropID', 'between', self::DanTou],
            ['l.Type', 'like', '赠送%']
        ];

        // 筛选炮倍
        if (!is_null($this->user_value_start) and !is_null($this->user_value_end)) {
            if ($this->user_field == 'cannon') {

                // 新增还是活跃
                if ($this->date_type == 'RegisterDate') {
                    // 所有的
                    $data = self::where($where)
                        ->fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                        ->alias('l')
                        ->group('l.PropID')
                        ->leftJoin('qpaccountsdb.user_game_info g', 'l.UserID = g.UserID')
                        ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID')

                        ->where($where_dan_tou)
                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->where('a.RegisterDate', 'between', $this->date_range)
                        ->select();


                    // 充值的
                    $data2 = self::where($where)
                        ->fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                        ->alias('l')
                        ->group('l.PropID')
                        ->leftJoin('qpaccountsdb.user_game_info g', 'l.UserID = g.UserID')
                        ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID')

                        ->where($where_dan_tou)
                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->where('a.RegisterDate', 'between', $this->date_range)
                        ->where('l.UserID in'.$this->trade_sub_sql)
                        ->select();

                } else {
                    // 登录子查询
                    $login_sub_sql = UserLoginModel::getUserIdsSubSql($this->date_range);

                    $data = self::where($where)
                        ->fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                        ->alias('l')
                        ->group('l.PropID')
                        ->leftJoin('qpaccountsdb.user_game_info g', 'l.UserID = g.UserID')
                        ->leftJoin('qplogdb.user_inout_log o', 'o.UserID = l.UserID')

                        ->where($where_dan_tou)
                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->where('l.UserID in'.$login_sub_sql)
                        ->select();


                    // 充值的
                    $data2 = self::where($where)
                        ->where($where_dan_tou)
                        ->fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                        ->alias('l')
                        ->group('l.PropID')
                        ->leftJoin('qpaccountsdb.user_game_info g', 'l.UserID = g.UserID')

                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->where('l.UserID in'.$login_sub_sql)
                        ->where('l.UserID in'.$this->trade_sub_sql)
                        ->select();
                }

            } else {// 不筛选炮倍
                // 新增还是活跃
                if ($this->date_type == 'RegisterDate') {
                    // 所有的
                    $data = self::where($where)
                        ->where($where_dan_tou)
                        ->fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                        ->alias('l')
                        ->group('l.PropID')
                        ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID')
                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->where('a.RegisterDate', 'between', $this->date_range)
                        ->select();


                    // 充值的
                    $data2 = self::where($where)
                        ->where($where_dan_tou)
                        ->fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                        ->alias('l')
                        ->group('l.PropID')
                        ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID')

                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->where('a.RegisterDate', 'between', $this->date_range)
                        ->where('l.UserID in'.$this->trade_sub_sql)
                        ->select();

                } else {
                    // 登录子查询
                    $login_sub_sql = UserLoginModel::getUserIdsSubSql($this->date_range);

                    $data = self::where($where)
                        ->fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                        ->alias('l')
                        ->group('l.PropID')
                        ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID')

                        ->where($where_dan_tou)
                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->where('l.UserID in'.$login_sub_sql)
                        ->select();


                    // 充值的
                    $data2 = self::where($where)
                        ->fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                        ->alias('l')
                        ->group('l.PropID')
                        ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID')

                        ->where($where_dan_tou)
                        ->where('l.RecordTime', 'between', $this->date_range)
                        ->where('l.UserID in'.$login_sub_sql)
                        ->where('l.UserID in'.$this->trade_sub_sql)
                        ->select();
                }
            }
        } else {
            // 新增还是活跃
            if ($this->date_type == 'RegisterDate') {
                // 所有的
                $data = self::fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                    ->alias('l')
                    ->group('l.PropID')
                    ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID')
                    ->where($where_dan_tou)
                    ->where('l.RecordTime', 'between', $this->date_range)
                    ->where('a.RegisterDate', 'between', $this->date_range)
                    ->select();

                // 充值的
                $data2 = self::fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                    ->alias('l')
                    ->group('l.PropID')
                    ->leftJoin('qpaccountsdb.accounts_info a', 'l.UserID = a.UserID')

                    ->where($where_dan_tou)
                    ->where('l.RecordTime', 'between', $this->date_range)
                    ->where('a.RegisterDate', 'between', $this->date_range)
                    ->where('l.UserID in'.$this->trade_sub_sql)
                    ->select();
            } else {
                // 登录子查询
                $login_sub_sql = UserLoginModel::getUserIdsSubSql($this->date_range);

                // 所有的
                $data = self::fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                    ->alias('l')
                    ->group('l.PropID')

                    ->where($where_dan_tou)
                    ->where('l.RecordTime', 'between', $this->date_range)
                    ->where('l.UserID in'.$login_sub_sql)
                    ->select();

                // 充值的
                $data2 = self::fieldRaw('l.PropID as PropID,count(distinct(l.UserID)) as number,abs(sum(l.VarCounts)) as counts')
                    ->alias('l')
                    ->group('l.PropID')

                    ->where($where_dan_tou)
                    ->where('l.RecordTime', 'between', $this->date_range)
                    ->where('l.UserID in'.$login_sub_sql)
                    ->where('l.UserID in'.$this->trade_sub_sql)
                    ->select();
            }

        }
        // echo self::getLastSql();

        return [$data, $data2];
    }
}